const seq = require('./seq')
const moment = require('moment');
const { QueryTypes } = require('sequelize');

class teamdoc {
  async updateDocSharesOrState(id,shares,sharenames,state,flag)//flag=0,update shares,flag=1 update state
  {
    let sql = ``
    if(flag==1)
    {
      sql = `update teamdoc set state=${state} where id=${id}`
    }
    else{
      sql = `update teamdoc set sharedUserIDs='${shares}',sharedUsers='${sharenames}' where id=${id}`
    }
    const ret = await seq.query(sql,
      {
        type: QueryTypes.UPDATE
      }
    )
    return ret
  }
  async getDocBydocid(id)
  {
    const sql = `select * from teamdoc where id=${id}`
    const ret = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return ret
  }
  async getallDocs(id){
    const sql = `
      select  td.*,u.realName,1 as owners  from teamdoc  td
      left join user  u
      on td.userid=u.id
      where td.isdelete=0 and td.userid=${id}
      order by updatedtime desc
    `
    const ret1 = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )

    const sql2 = `
      select  td.*,u.realName,0 as owners  from teamdoc  td
      left join user  u
      on td.userid=u.id
      where td.isdelete=0 and td.userid<>${id} and td.state=1 and find_in_set(${id},td.sharedUserIDs)
      order by updatedtime desc
    `
    const ret2 = await seq.query(sql2,
      {
        type: QueryTypes.SELECT
      }
    )
    const results=ret1.concat(ret2)
    return results
  }
  async getDocByname(docname){
    const sql = `select  *  from teamdoc  where isdelete=0 and docname="${docname}"`
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return results
  }

  async saveteamdoc(data,savepath,url,docname,originName){
    const {id,userID,state=0,sharedUserIDs=""} =data
    const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
    if(id<1){//新增
      const sql = `insert into teamdoc 
                  set docname=?,userID=?,savepath=?,url=?,originName=?,state=?`
      const results = await seq.query(sql,
        {
          replacements: [docname,userID,savepath,url,originName,state],
          type: QueryTypes.INSERT
        }
      )
      return results
    }else{
      const sql = `update teamdoc 
                  set docname=?,originName=?,state=?,updatedtime=?,sharedUserIDs=?,savepath=?,url=?
                  where id=${id}`
      const results = await seq.query(sql,
        {
          replacements: [docname,originName,state,currentTime,sharedUserIDs,savepath,url],
          type: QueryTypes.UPDATE
        }
      )
      return results
    }
  }

  async saveteamdocInput(data,savepath,url,docname){
    const {id,userID,teamdocid} =data
    const sqlexist= `select id from teamdocSaveHistory where teamdocid=${teamdocid} and userID=${userID} 
                      order by savetime desc limit 1`
    const exret = await seq.query(sqlexist,{type: QueryTypes.SELECT})
    //if(results.length>0){//修改
    const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
    if(exret && exret.length>0)
    {
      const sql = `update teamdocSaveHistory 
                  set teamdocid=?,docname=?,userid=?,savetime=?,savepath=?,url=?
                  where id=${exret[0].id}`
      const results = await seq.query(sql,
        {
          replacements: [teamdocid,docname,userID,currentTime,savepath,url],
          type: QueryTypes.UPDATE
        }
      )
      return results
    }else{
      const sql = `insert into teamdocSaveHistory 
                  set teamdocid=?,docname=?,userid=?,savetime=?,savepath=?,url=?`
      const results = await seq.query(sql,
        {
            replacements: [teamdocid,docname,userID,currentTime,savepath,url],
            type: QueryTypes.INSERT
        }
      )
      return results
    }
    /*
    if(id<1){//新增 id<1 废弃
      const sql = `insert into teamdocSaveHistory 
                  set teamdocid=?,docname=?,userid=?,savetime=?,savepath=?,url=?`
      const results = await seq.query(sql,
        {
          replacements: [teamdocid,docname,userID,currentTime,savepath,url],
          type: QueryTypes.INSERT
        }
      )
      return results
    }else{
      const sql = `update teamdocSaveHistory 
                  set teamdocid=?,docname=?,userid=?,savetime=?,savepath=?,url=?
                  where id=${id}`
      const results = await seq.query(sql,
        {
          replacements: [teamdocid,docname,userID,currentTime,savepath,url],
          type: QueryTypes.UPDATE
        }
      )
      return results
    }*/
  }

  async getDocInputBydocTplid(id,flag=0)
  {
    let sql=""
    sql = `
          select savepath, createdtime as savetime,id,0 as tpl  from teamdoc where id =${id}
          union
          select savepath ,savetime,id,1 as tpl  from teamdocSaveHistory where teamdocid=${id}
          order by savetime desc limit 1
          `
    const ret = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return ret
  }

  async getDocsHistoryBydocTplid(id)
  {
    const sql = `
              select th.docname, th.id,th.teamdocid,th.savepath ,th.savetime ,user.realName,user.id as userid,0 as istpl from teamdocSaveHistory th 
              left join  user on th.userid=user.id
              where th.teamdocid=${id} and th.isdelete=0
              order by savetime desc
                `
    const ret = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return ret
  }

  async delteamdocbytplid(id)
  {
    let sql = `
              update teamdoc SET isdelete=1
              where id=${id}
                `
    await seq.query(sql,
      {
        type: QueryTypes.UPDATE
      }
    )
    sql = `
              update teamdocSaveHistory SET isdelete=1
              where teamdocid=${id}
                `
    await seq.query(sql,
      {
        type: QueryTypes.UPDATE
      }
    )
  }

  async delteamdocHis(id)
  {
    const sql = `
              update teamdocSaveHistory SET isdelete=1
              where id=${id}
                `
    await seq.query(sql,
      {
        type: QueryTypes.UPDATE
      }
    )
  }
}


module.exports = new teamdoc()